package hotel.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import hotel.dao.RequestDao;
import hotel.entity.RequestEntity;

public class RequestDaoImpl implements RequestDao 
{
	private static final Logger logger = Logger.getLogger(RequestDaoImpl.class);
	private static final String ERROR = "Error in RequestDaoImpl:";
	
	private static final String GET_REQUESTS = "select id, roomClass, beds, name, surname, username, status from requests";
	private static final String GET_REQUESTS_BY_ID = "select id, roomClass, beds, name, surname, username from requests where id=?";
	private static final String GET_REQUESTS_BY_USERNAME = "select id, roomClass, beds, name, surname, username from requests where username=?";
	private static final String INCERT_REQUEST = "insert requests(roomClass, beds, name, surname, username) "
			+ "values (?, ?, ?, ?, ?)";
	private static final String DELETE_REQUEST = "delete from requests where id=?";
	private static final String UPDATE_DELETE_STATUS = "update requests set status = ? where id = ?";
	private static final String GET_REQUEST_STATUS = "select status from requests where id = ?";

	private Connection conn;
	
	public RequestDaoImpl(Connection conn)
	{
		this.conn = conn;
	}
	
	@Override
	public List<RequestEntity> findAll() throws SQLException
	{
		List<RequestEntity> requestEntities = new ArrayList<RequestEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = conn.prepareStatement(RequestDaoImpl.GET_REQUESTS);
			rs = stmt.executeQuery();
			while (rs.next())
			{ 
				if (rs.getString("status") == null)
					requestEntities.add(new RequestEntity(
								rs.getInt("id"),
								rs.getString("roomClass"),
								rs.getInt("beds"),
								rs.getString("name"),
								rs.getString("surname"),
								rs.getString("username")));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		
		return requestEntities;
	}
	
	@Override
	public RequestEntity findById(int id) throws SQLException 
	{
		RequestEntity requestEntity = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = conn.prepareStatement(RequestDaoImpl.GET_REQUESTS_BY_ID);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next())
			{ 
				requestEntity = new RequestEntity(
							rs.getInt("id"),
							rs.getString("roomClass"),
							rs.getInt("beds"),
							rs.getString("name"),
							rs.getString("surname"),
							rs.getString("username"));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		return requestEntity;
	}

	@Override
	public void insert(final RequestEntity entity) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement(RequestDaoImpl.INCERT_REQUEST);
			
			stmt.setString(1, entity.getRoomClass());
			stmt.setInt(2, entity.getBeds());
			stmt.setString(3, entity.getName());
			stmt.setString(4, entity.getSurname());
			stmt.setString(5, entity.getUsername());
			
			stmt.executeUpdate();
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
	}
	
	
	@Override
	public void update(RequestEntity entity) {
		// TODO Auto-generated method stub
	}

	@Override
	public void changeStatus(int id, String status) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement(RequestDaoImpl.UPDATE_DELETE_STATUS);
			stmt.setString(1, status);
			stmt.setInt(2, id);
			stmt.executeUpdate();
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
	}

	@Override
	public void delete(int id) throws SQLException 
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement(RequestDaoImpl.DELETE_REQUEST);
			stmt.setInt(1, id);
			stmt.executeUpdate();
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }

	}
	
	@Override
	public String checkStatus(int id) throws SQLException
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String status = null;
		try
		{
			stmt = conn.prepareStatement(RequestDaoImpl.GET_REQUEST_STATUS);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next())
				status = rs.getString("status");
			return status;
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
	}
	
	public RequestEntity findByUsername(final String username) throws SQLException
	{
		RequestEntity requestEntity = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = conn.prepareStatement(RequestDaoImpl.GET_REQUESTS_BY_USERNAME);
			stmt.setString(1, username);
			rs = stmt.executeQuery();
			if (rs.next())
			{ 
				requestEntity = new RequestEntity(
							rs.getInt("id"),
							rs.getString("roomClass"),
							rs.getInt("beds"),
							rs.getString("name"),
							rs.getString("surname"),
							rs.getString("username"));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		return requestEntity;
	}
}